package com.vinaprime.primeschedule.databaselayer;

import java.text.DateFormat;
import java.text.SimpleDateFormat;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.vinaprime.primeschedule.entities.VPProject;

/**
 * @author NGOCTB
 * Khai bao doi tuong tuong tac voi table Project, gom cac thuoc tinh cua table va cac phuong thuc thao tac du lieu
 */
@SuppressLint("NewApi")
public class VPProject_DL {
	//	NgocTB: Khai bao cau truc cua table 
	private static final String DB_TABLE_NAME = "VP_PROJECT";
	private static final String DB_TABLE_COL_ROWID = "_id";
	private static final String DB_TABLE_COL_ID = "VPP_ID";
	private static final String DB_TABLE_COL_CODE = "VPP_CODE";
	private static final String DB_TABLE_COL_NAME = "VPP_NAME";
	private static final String DB_TABLE_COL_DESCRIPTION = "VPP_DESCRIPTION";
	private static final String DB_TABLE_COL_ENABLE_FLAG = "VPP_ENABLE_FLAG";
	private static final String DB_TABLE_COL_START_DATE = "VPP_START_DATE";
	private static final String DB_TABLE_COL_END_DATE = "VPP_END_DATE";
	
	// NgocTB: Khai bao dinh dang luu tru ngay thang trong Database
	private static final String DB_DateFormat = "MM/dd/yyyy";
	
	private DatabaseController_DL myDatabaseControllerDL;
	private DateFormat myDateFormat;
	
	public VPProject_DL(Context context, String path){
		this.myDatabaseControllerDL = new DatabaseController_DL(context, path);		
		this.myDateFormat = new SimpleDateFormat(DB_DateFormat);
	}
	
	public static String getDbTableName() {
		return DB_TABLE_NAME;
	}
	
	public static String getDbTableColRowId() {
		return DB_TABLE_COL_ROWID;
	}
	
	public static String getDbTableColId() {
		return DB_TABLE_COL_ID;
	}

	public static String getDbTableColCode() {
		return DB_TABLE_COL_CODE;
	}

	public static String getDbTableColName() {
		return DB_TABLE_COL_NAME;
	}

	public static String getDbTableColDescription() {
		return DB_TABLE_COL_DESCRIPTION;
	}

	public static String getDbTableColEnableFlag() {
		return DB_TABLE_COL_ENABLE_FLAG;
	}

	public static String getDbTableColStartDate() {
		return DB_TABLE_COL_START_DATE;
	}

	public static String getDbTableColEndDate() {
		return DB_TABLE_COL_END_DATE;
	}
	
	public static String getDbDateformat() {
		return DB_DateFormat;
	}

	public Cursor getAllProject(){
		SQLiteDatabase db = this.myDatabaseControllerDL.getWritableDatabase();
		
		String selectQuery = "SELECT  " + this.DB_TABLE_COL_ID + " AS " + this.DB_TABLE_COL_ROWID + ", " +
											this.DB_TABLE_COL_ID + ", " +
											this.DB_TABLE_COL_CODE + ", " +
											this.DB_TABLE_COL_NAME + ", " +
											this.DB_TABLE_COL_DESCRIPTION + ", " +
											this.DB_TABLE_COL_ENABLE_FLAG + ", " +
											this.DB_TABLE_COL_START_DATE + ", " +
											this.DB_TABLE_COL_END_DATE +
								" FROM " + DB_TABLE_NAME +
								" WHERE 1 = 1 " +
								" ORDER BY " + this.DB_TABLE_COL_CODE + " DESC";
		
		Cursor cur = db.rawQuery(selectQuery, null);
		
		return cur;
	}
	
	public Cursor getNewProjectId()
	{
		SQLiteDatabase db = this.myDatabaseControllerDL.getWritableDatabase();
		
		String selectQuery = "SELECT MAX(" + DB_TABLE_COL_ID + ") AS " + DB_TABLE_COL_ID + " FROM " + DB_TABLE_NAME;
		
		Cursor cur = db.rawQuery(selectQuery, null);
		
		return cur;
	}
	
	public Cursor getProject(int id){
		SQLiteDatabase db = this.myDatabaseControllerDL.getWritableDatabase();
		
		String selectQuery = "SELECT  " + this.DB_TABLE_COL_ID + " AS " + this.DB_TABLE_COL_ROWID + ", " +
											this.DB_TABLE_COL_ID + ", " +
											this.DB_TABLE_COL_CODE + ", " +
											this.DB_TABLE_COL_NAME + ", " +
											this.DB_TABLE_COL_DESCRIPTION + ", " +
											this.DB_TABLE_COL_ENABLE_FLAG + ", " +
											this.DB_TABLE_COL_START_DATE + ", " +
											this.DB_TABLE_COL_END_DATE +
								" FROM " + DB_TABLE_NAME +
								" WHERE 1 = 1 AND " + this.DB_TABLE_COL_ID + " = " + id;
		
		Cursor cur = db.rawQuery(selectQuery, null);
		
		return cur;
	}
	
	public Cursor getProject(String keyWord){
		SQLiteDatabase db = this.myDatabaseControllerDL.getWritableDatabase();
		
		String selectQuery = "SELECT  " + this.DB_TABLE_COL_ID + " AS " + this.DB_TABLE_COL_ROWID + ", " +
											this.DB_TABLE_COL_ID + ", " +
											this.DB_TABLE_COL_CODE + ", " +
											this.DB_TABLE_COL_NAME + ", " +
											this.DB_TABLE_COL_DESCRIPTION + ", " +
											this.DB_TABLE_COL_ENABLE_FLAG + ", " +
											this.DB_TABLE_COL_START_DATE + ", " +
											this.DB_TABLE_COL_END_DATE +
								" FROM " + DB_TABLE_NAME +
								" WHERE 1 = 1 " +
								" AND (LOWER(" + this.DB_TABLE_COL_NAME + ") LIKE '%' || LOWER('" + keyWord + "') || '%' OR " +
								" LOWER(" + this.DB_TABLE_COL_CODE + ") LIKE '%' || LOWER('" + keyWord + "') || '%') " +
								" ORDER BY " + this.DB_TABLE_COL_CODE + " DESC";
		
		Cursor cur = db.rawQuery(selectQuery, null);
		
		return cur;
	}
	
	public void addProject(VPProject project) {
		SQLiteDatabase db = this.myDatabaseControllerDL.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(DB_TABLE_COL_ID, project.getVppId());
        values.put(DB_TABLE_COL_CODE, project.getVppCode());
        values.put(DB_TABLE_COL_NAME, project.getVppName());
        values.put(DB_TABLE_COL_DESCRIPTION, project.getVppDescription());
        values.put(DB_TABLE_COL_ENABLE_FLAG, project.getVppEnableFlag());

        if (project.getVppStartDate() != null)
        	values.put(DB_TABLE_COL_START_DATE, myDateFormat.format(project.getVppStartDate()));
        else
        	values.putNull(DB_TABLE_COL_START_DATE);
        
        if (project.getVppEndDate() != null)
        	values.put(DB_TABLE_COL_END_DATE, myDateFormat.format(project.getVppEndDate()));
        else
        	values.putNull(DB_TABLE_COL_END_DATE);
 
        // Inserting Row
        db.insert(DB_TABLE_NAME, null, values);
        db.close(); // Closing database connection
    }
	
	public void updateProject(VPProject project) {
        DateFormat dateFormat = DateFormat.getDateInstance();
		SQLiteDatabase db = this.myDatabaseControllerDL.getWritableDatabase();
        
        // updating row
        ContentValues values = new ContentValues();
        values.put(DB_TABLE_COL_ID, project.getVppId());
        values.put(DB_TABLE_COL_CODE, project.getVppCode());
        values.put(DB_TABLE_COL_NAME, project.getVppName());
        values.put(DB_TABLE_COL_DESCRIPTION, project.getVppDescription());
        values.put(DB_TABLE_COL_ENABLE_FLAG, project.getVppEnableFlag());
        
        if (project.getVppStartDate() != null)
        	values.put(DB_TABLE_COL_START_DATE, myDateFormat.format(project.getVppStartDate()));
        else
        	values.putNull(DB_TABLE_COL_START_DATE);
        
        if (project.getVppEndDate() != null)
        	values.put(DB_TABLE_COL_END_DATE, myDateFormat.format(project.getVppEndDate()));
        else
        	values.putNull(DB_TABLE_COL_END_DATE);
        
        db.update(this.DB_TABLE_NAME, values, DB_TABLE_COL_ID + " = ?", new String[] {String.valueOf(project.getVppId())});
        db.close(); // Closing database connection
    }
 
    // Deleting single Project
    public void deleteProject(int projectId) {
        SQLiteDatabase db = this.myDatabaseControllerDL.getWritableDatabase();
        
        db.delete(DB_TABLE_NAME, DB_TABLE_COL_ID + " = ?",
                new String[] { String.valueOf(projectId) });
        db.close();
    }
}
